﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Groep8
{
    /// <summary>
    /// Deze klasse zorgt voor interactie met de database aan de hand van stored procedures
    /// </summary>
    public class DataAccessLayer
    {
        private SqlConnection conFestival;
        private SqlCommand cmdFestival;
        private SqlDataAdapter adpFestival = new SqlDataAdapter();
        private DataTable dtFestival = new DataTable();
        private SqlTransaction sqlTransaction;

        public DataAccessLayer()
        {
            //add constructor logic??
            CreateConnection();
        }

        public String ConnectionString
        {
            get
            {

             return ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            }


         
        }

        private void CreateConnection()
        {
            conFestival = new SqlConnection(ConnectionString);
        }

        private void ProcedureCall(String procedure)
        {
            cmdFestival = new SqlCommand(procedure, conFestival);
            cmdFestival.CommandType = CommandType.StoredProcedure;

            adpFestival = new SqlDataAdapter(cmdFestival);
            dtFestival = new DataTable();

            conFestival.Open();

            sqlTransaction = conFestival.BeginTransaction();
            cmdFestival.Transaction = sqlTransaction;
        }

        private void CloseConnection()
        {
            if (conFestival.State == ConnectionState.Open)
                conFestival.Close();
        }


        //Stored procedure call met naam van procedure en id --> Artist & Band pagina

        public DataTable Select(String naam)
        {
            try
            {
                ProcedureCall(naam);
                adpFestival.Fill(dtFestival);


            }
            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }

            try
            {
                CloseConnection();
            }
            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }
            return dtFestival;
        }

        public DataTable Select(String naam, int id)
        {
            try
            {
                conFestival.Open();

                using (SqlCommand cmdFestival = new SqlCommand(naam , conFestival))
                {
                    cmdFestival.CommandType = CommandType.StoredProcedure;
                    cmdFestival.Parameters.AddWithValue("@id", id);

                    adpFestival.SelectCommand = cmdFestival;
                    adpFestival.Fill(dtFestival);

                }
            }

            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }

            try
            {
                CloseConnection();
            }
            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }
            return dtFestival;
        }





        private void ProcedureCallWithParam(String procedure, int  param)
        {
            cmdFestival = new SqlCommand(procedure, conFestival);
            cmdFestival.CommandType = CommandType.StoredProcedure;
            cmdFestival.Parameters.AddWithValue("@Param", param); 

            adpFestival = new SqlDataAdapter(cmdFestival);
            dtFestival = new DataTable();

            conFestival.Open();

            sqlTransaction = conFestival.BeginTransaction();
            cmdFestival.Transaction = sqlTransaction;

            try
            {
                CloseConnection();
            }
            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }

        }

        // Stored procedure call met naam en parameter

        public DataTable SelectWithParam(String naam, int param)
        {
            try
            {
                ProcedureCallWithParam(naam, param);
                adpFestival.Fill(dtFestival);

            }
            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }

            try
            {
                CloseConnection();
            }
            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }
            return dtFestival;
        }

        public void AddFestival(string name, string location, String date, string country, int duration)
        {
            try
            {
                ProcedureCall("InsertFestivals");
                SqlParameter param = new SqlParameter("@fest_naam", name);
                cmdFestival.Parameters.Add(param);
                param = new SqlParameter("@fest_country", country);
                cmdFestival.Parameters.Add(param);
                param = new SqlParameter("@fest_datum", date);
                cmdFestival.Parameters.Add(param);
                param = new SqlParameter("@fest_duur",duration);
                cmdFestival.Parameters.Add(param);
                param = new SqlParameter("@fest_locatie",location);
                cmdFestival.Parameters.Add(param);
                adpFestival.Fill(dtFestival);
                sqlTransaction.Commit();
                
            }
            catch (SqlException ex)
            {
                if (sqlTransaction != null)
                    sqlTransaction.Rollback();
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }

            try
            {
                CloseConnection();
            }
            catch (SqlException ex)
            {
                dtFestival = null;
                Console.WriteLine("Error " + ex);
            }
        }


    }
}